{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "943af7d8",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "from scipy.optimize import minimize"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ca96bda5",
   "metadata": {},
   "source": [
    "## 开始优化\n",
    "现在有数据：<br />\n",
    "1. 沪深300指数数据：df300\n",
    "2. 沪深300指数近3年每天的数据：df300_daily\n",
    "3. 成分股近3年每天的数据：df\n",
    "4. 成分股近3年涨跌幅时序数据：df1\n",
    "5. 指数权重前20支股票的代码与权重值：df20\n",
    "<br />\n",
    "\n",
    "目标是最小化跟踪误差+涨跌幅绝对误差，找到一组不超过20支股票买入的权重w"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "4c3d8e0c",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "D:\\Anaconda3\\lib\\site-packages\\openpyxl\\styles\\stylesheet.py:221: UserWarning: Workbook contains no default style, apply openpyxl's default\n",
      "  warn(\"Workbook contains no default style, apply openpyxl's default\")\n"
     ]
    }
   ],
   "source": [
    "# 指数权重前20的成分股近3年每天的数据\n",
    "df=pd.read_excel(\"df_all_top_20.xlsx\",index_col=0)\n",
    "# 指数权重前20的成分股近3年收盘时序数据\n",
    "df1=pd.pivot_table(df,index=[\"trade_date\",\"ts_code\"],values=[\"pct_chg\"])\n",
    "df2=pd.pivot_table(df,index=[\"trade_date\",\"ts_code\"],values=[\"close\",\"pre_close\"])\n",
    "# 沪深300指数基本数据\n",
    "df300=pd.read_excel(\"df_300_w.xlsx\",index_col=0)\n",
    "df20=df300.loc[:19,:] # 指数权重前20的成分股基本数据\n",
    "# 沪深300指数近3年每天的数据\n",
    "df300_daily=pd.read_excel('沪深300指数近3年.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "b203b088",
   "metadata": {},
   "outputs": [],
   "source": [
    "ts_code_20=df20['ts_code'].tolist()\n",
    "close300=df300_daily[['日期Date','收盘Close']]\n",
    "chg300=df300_daily[['日期Date','涨跌幅(%)Change(%)']]\n",
    "index2codes=ts_code_20\n",
    "closei=df2\n",
    "chgi=df1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0d26e05e",
   "metadata": {},
   "source": [
    "## 定义目标函数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "e5a16659",
   "metadata": {},
   "outputs": [],
   "source": [
    "def objective1(x):\n",
    "    # 最小化涨跌幅绝对误差之和\n",
    "    days=len(chg300)\n",
    "    abs_error=0\n",
    "    for i in range(days):\n",
    "        # 计算20支股票组合的当日涨跌幅\n",
    "        chg_i=0\n",
    "        date=chg300.loc[i,'日期Date']\n",
    "        str_query='trade_date == ['+ str(date)+']'\n",
    "        df_i=chgi.query(str_query)\n",
    "        df_i=df_i.reset_index()\n",
    "        for j in range(len(x)):\n",
    "            # 根据索引找到股票代码，然后找到当日chg\n",
    "            item=df_i[df_i['ts_code']==index2codes[j]]\n",
    "            if not item.empty:\n",
    "                chg_i_j=np.sum(item['pct_chg']/100)\n",
    "            else:\n",
    "                # 这只股票当天没数据的话就等于0\n",
    "                chg_i_j=0\n",
    "            chg_i+=chg_i_j*x[j]\n",
    "        # 找到当日沪深300的涨跌幅\n",
    "        chg300_i=chg300.loc[i,'涨跌幅(%)Change(%)']/100\n",
    "        # 当日涨跌幅绝对误差(单位：1)\n",
    "        abs_error_i=np.abs(chg300_i-chg_i)\n",
    "        # 求和\n",
    "        abs_error+=abs_error_i\n",
    "    return abs_error\n",
    "\n",
    "def objective2(x):\n",
    "    # 最小化跟踪误差\n",
    "    days=len(close300)\n",
    "    track_error=0\n",
    "    earning_rate=[]\n",
    "    for i in range(days):\n",
    "        if i==0:\n",
    "            continue\n",
    "        # 计算20支股票组合的当日收益率：(当日收盘价 - 前一日收盘价) / 前一日收盘价\n",
    "        earning_rate_i=0\n",
    "        date=close300.loc[i,'日期Date']\n",
    "        str_query='trade_date == ['+ str(date)+']'\n",
    "        df_i=closei.query(str_query)\n",
    "        df_i=df_i.reset_index()\n",
    "        for j in range(len(x)):\n",
    "            # 根据索引找到股票代码，然后计算当日收益率\n",
    "            item=df_i[df_i['ts_code']==index2codes[j]]\n",
    "            if not item.empty:\n",
    "                earning_rate_i_j=np.sum((item['close']-item['pre_close'])/item['pre_close'])\n",
    "            else:\n",
    "                # 这只股票当天没数据的话就等于0\n",
    "                earning_rate_i_j=0\n",
    "            earning_rate_i+=earning_rate_i_j*x[j]\n",
    "        # 找到当日沪深300的收益率\n",
    "        earning_rate300_i=(close300.loc[i,'收盘Close']-close300.loc[i-1,'收盘Close'])/close300.loc[i-1,'收盘Close']\n",
    "        # 当日跟踪误差误差(单位：1)\n",
    "        earning_rate.append(earning_rate_i-earning_rate300_i)\n",
    "    # 求标准差\n",
    "    track_error=np.std(earning_rate)\n",
    "    return track_error\n",
    "\n",
    "def objective(x):\n",
    "    return 0.5*objective1(x)+0.5*objective2(x)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "81dbbd2a",
   "metadata": {},
   "source": [
    "## 定义约束条件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "d46be8df",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 定义约束条件\n",
    "def constraint1(x):\n",
    "    return np.sum(x)-1        # 等式约束  "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "661c7e93",
   "metadata": {},
   "source": [
    "## 给出变量取值范围"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "45ccb552",
   "metadata": {},
   "outputs": [],
   "source": [
    "nums=20\n",
    "bounds=[]\n",
    "for _ in range(nums):\n",
    "    bounds.append((0.0,1))\n",
    "bounds=tuple(bounds)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bebbb29a",
   "metadata": {},
   "source": [
    "## 开始优化"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "eb279106",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "目标值: 1.0284831630055231\n",
      "最优解为\n",
      "[1.26542513e-01 1.10717021e-01 6.20618697e-02 4.32622000e-02\n",
      " 5.75416181e-02 3.30119331e-02 2.94424955e-02 6.57879861e-02\n",
      " 3.30119331e-02 3.46033441e-02 7.50051637e-02 3.30119331e-02\n",
      " 6.42394278e-02 1.44440677e-05 8.19757105e-03 3.30119331e-02\n",
      " 3.30119331e-02 6.32862646e-02 6.12264834e-02 3.30119331e-02]\n"
     ]
    }
   ],
   "source": [
    "con1 = {'type': 'eq', 'fun': constraint1}\n",
    "cons = ([con1])  \n",
    "\n",
    "x0=np.array([0.05]*20) #定义初始值\n",
    "solution = minimize(objective, x0, method='SLSQP', \\\n",
    "                bounds=bounds, constraints=cons)\n",
    "\n",
    "x = solution.x\n",
    "\n",
    "print('目标值: ' + str(objective(x)))\n",
    "print('最优解为')\n",
    "print(x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "4fd26bba",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "     fun: 1.0284831630055231\n",
       "     jac: array([-0.01607668, -0.01397853, -0.03082941, -0.03791852, -0.02950577,\n",
       "        0.        ,  0.01917057, -0.03980808,  0.        ,  0.00562067,\n",
       "       -0.01060319,  0.        , -0.00674191,  0.00611918,  0.01699074,\n",
       "        0.        ,  0.        , -0.0188206 , -0.00171901,  0.        ])\n",
       " message: 'Optimization terminated successfully'\n",
       "    nfev: 712\n",
       "     nit: 32\n",
       "    njev: 32\n",
       "  status: 0\n",
       " success: True\n",
       "       x: array([1.26542513e-01, 1.10717021e-01, 6.20618697e-02, 4.32622000e-02,\n",
       "       5.75416181e-02, 3.30119331e-02, 2.94424955e-02, 6.57879861e-02,\n",
       "       3.30119331e-02, 3.46033441e-02, 7.50051637e-02, 3.30119331e-02,\n",
       "       6.42394278e-02, 1.44440677e-05, 8.19757105e-03, 3.30119331e-02,\n",
       "       3.30119331e-02, 6.32862646e-02, 6.12264834e-02, 3.30119331e-02])"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "solution"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c3ae7d4d",
   "metadata": {},
   "source": [
    "## 评价指标"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "1b11df0c",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_total_return(x):\n",
    "    # 计算收益率\n",
    "    days=len(close300)\n",
    "    total_return=0\n",
    "    daily_returns=[]\n",
    "    track_error=0\n",
    "    earning_rate=[]\n",
    "    for i in range(days):\n",
    "        if i==0:\n",
    "            continue\n",
    "        # 计算20支股票组合的当日收益率：(当日收盘价 - 前一日收盘价) / 前一日收盘价\n",
    "        earning_rate_i=0\n",
    "        date=close300.loc[i,'日期Date']\n",
    "        str_query='trade_date == ['+ str(date)+']'\n",
    "        df_i=closei.query(str_query)\n",
    "        df_i=df_i.reset_index()\n",
    "        for j in range(len(x)):\n",
    "            # 根据索引找到股票代码，然后计算当日收益率\n",
    "            item=df_i[df_i['ts_code']==index2codes[j]]\n",
    "            if not item.empty:\n",
    "                earning_rate_i_j=np.sum((item['close']-item['pre_close'])/item['pre_close'])\n",
    "            else:\n",
    "                # 这只股票当天没数据的话就等于0\n",
    "                earning_rate_i_j=0\n",
    "            earning_rate_i+=earning_rate_i_j*x[j]\n",
    "\n",
    "        # 找到当日沪深300的收益率\n",
    "        earning_rate300_i=(close300.loc[i,'收盘Close']-close300.loc[i-1,'收盘Close'])/close300.loc[i-1,'收盘Close']\n",
    "        # 当日跟踪误差误差(单位：1)\n",
    "        earning_rate.append(earning_rate_i-earning_rate300_i)\n",
    "        total_return+=earning_rate_i\n",
    "        daily_returns.append(earning_rate_i)\n",
    "    daily_returns=np.array(daily_returns)\n",
    "    # 求标准差\n",
    "    track_error=np.std(earning_rate)\n",
    "    return total_return,daily_returns,track_error"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "2d58b131",
   "metadata": {},
   "outputs": [],
   "source": [
    "eva_index={}\n",
    "ts_code_20=df20['ts_code'].tolist()\n",
    "close300=df300_daily[['日期Date','收盘Close']]\n",
    "index2codes=ts_code_20\n",
    "df2=pd.pivot_table(df,index=[\"trade_date\",\"ts_code\"],values=[\"close\",\"pre_close\"])\n",
    "closei=df2\n",
    "# 该组合跟踪误差\n",
    "total_return,daily_returns,track_error=get_total_return(x)\n",
    "eva_index['跟踪误差']=track_error\n",
    "\n",
    "# 年化收益率\n",
    "n=3 # 取的是近3年的数据\n",
    "annualized_return = (1 + total_return) ** (1 / n) - 1\n",
    "eva_index['年化收益率']=annualized_return\n",
    "\n",
    "# 年化波动率\n",
    "# 计算日波动率（日收益率的标准差）\n",
    "daily_volatility=np.std(daily_returns, ddof=1)\n",
    "# 将日波动率年化\n",
    "annualized_volatility=daily_volatility * np.sqrt(252)\n",
    "eva_index['年化波动率']=annualized_volatility\n",
    "\n",
    "# 夏普比率=（策略年化收益率 - 无风险年化收益率 ） / 策略年化波动率\n",
    "R_p = annualized_return  # 投资组合年化回报率\n",
    "R_f = 0.0125  # 无风险利率：3年期国债收益率\n",
    "sigma_p = annualized_volatility  # 投资组合的年化波动率\n",
    "sharpe_ratio = (R_p - R_f) / sigma_p\n",
    "eva_index['夏普比率']=sharpe_ratio"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "be739a0d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'跟踪误差': 0.0037917424153070716,\n",
       " '年化收益率': 0.05438813449649205,\n",
       " '年化波动率': 0.17354697613452139,\n",
       " '夏普比率': 0.24136481907943658}"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "eva_index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0354908e",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ac590e0b",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "86049c9d",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
